﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Collections;

namespace CalemShop
{
    public partial class FrmImportData : Form
    {
        string connectStringPattern = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\";";
        string connectString = null;
        string sheetName = string.Empty;
        OleDbConnection conn = null;
        ArrayList sqlList = new ArrayList();
        public FrmImportData()
        {
            CheckForIllegalCrossThreadCalls = false;
            InitializeComponent();
        }

        private void FrmImportData_Load(object sender, EventArgs e)
        {
            progressBar.Hide();
        }

        private void btBrowse_Click(object sender, EventArgs e)
        {
            try
            {
                if (openFileDialog.ShowDialog(this) == DialogResult.OK)
                {
                    backgroundWorker.RunWorkerAsync();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        void getDataFormExcelFile()
        {
            OleDbDataReader rs = null;
            StringBuilder builder = new StringBuilder();
            try
            {
                sqlList.Clear();
                string sql = "select * from [{0}]";
                sql = string.Format(sql, sheetName);
                OleDbCommand selectCmd = new OleDbCommand(sql, conn);
                rs = selectCmd.ExecuteReader();
                int count = 0;
                while (rs.Read())
                {
                    backgroundWorker.ReportProgress(++count);
                    string maso = string.Empty;
                    string name = string.Empty;
                    double amount = 0;
                    double autoKey = 0;
                    string gender = "T";
                    string color = string.Empty;
                    string size = string.Empty;
                    string supplier = string.Empty;
                    double type = 0;
                    double cost = 0;
                    double price = 0;
                    double giasi = 0;


                    if (rs.GetValue(0) != DBNull.Value)
                    {
                        maso = rs.GetString(0);
                    }
                    else
                    {
                        continue;
                    }

                    if (rs.GetValue(12) != DBNull.Value)
                    {
                        autoKey = rs.GetDouble(12);
                        if (autoKey <= 0)
                        {
                            continue;
                        }
                    }
                    else
                    {
                        continue;
                    }

                    if (rs.GetValue(1) != DBNull.Value)
                    {
                        name = rs.GetString(1);
                    }

                    if (rs.GetValue(2) != DBNull.Value)
                    {
                        amount = rs.GetDouble(2);
                        if (amount <= 0)
                        {
                            continue;
                        }
                    }
                    else
                    {
                        continue;
                    }

                    if (rs.GetValue(4) != DBNull.Value)
                    {
                        type = rs.GetDouble(4);
                    }

                    if (rs.GetValue(5) != DBNull.Value)
                    {
                        gender = rs.GetString(5);
                    }

                    if (rs.GetValue(6) != DBNull.Value)
                    {
                        size = rs.GetValue(6).ToString();
                    }

                    if (rs.GetValue(7) != DBNull.Value)
                    {
                        color = rs.GetString(7);
                    }

                    if (rs.GetValue(8) != DBNull.Value)
                    {
                        cost = rs.GetDouble(8);
                    }

                    if (rs.GetValue(9) != DBNull.Value)
                    {
                        price = rs.GetDouble(9);
                    }

                    if (rs.GetValue(10) != DBNull.Value)
                    {
                        giasi = rs.GetDouble(10);
                    }

                    if (rs.GetValue(11) != DBNull.Value)
                    {
                        supplier = rs.GetValue(11).ToString();
                    }

                    name = name.Replace("'", @"\'");
                    color = color.Replace("'", @"\'");
                    supplier = supplier.Replace("'", @"\'");
                    gender = gender.Replace("'", @"\'");
                    //string cmd = string.Format("insert into shop_stock(`autoKey`,`itemId`,`itemName`,`amount`,`type`,`gender`,`size`,`color`,`cost`,`price`,`giasi`,`supplier`) values('{15}','{0}','{1}',{2},{3},'{4}','{5}','{6}',{7},{8},{9},'{10}') ON DUPLICATE KEY UPDATE amount = amount + {11}, cost = {12}, price = {13}, giasi={14};",
                    //    maso, name, amount, type, gender, size, color, cost, price, giasi, supplier, amount, cost, price, giasi, autoKey);


                    
                    string cmd = string.Format("insert ignore into shop_stock(`autoKey`,`itemId`,`itemName`,`amount`,`type`,`gender`,`size`,`color`,`cost`,`price`,`giasi`,`supplier`,`createTime`) values('{0}','{1}','{2}',{3},{4},'{5}','{6}','{7}',{8},{9},{10},'{11}','{12}');",
                        autoKey, maso, name, amount, type, gender, size, color, cost, price, giasi, supplier, DateTime.Now.ToString("yyyy-MM-dd"));
                    if (rdUpdate.Checked)
                    {
                        cmd = string.Format("update shop_stock set `amount` =  `amount` + {0},`price` = {1} where autoKey = {2}",
                        amount, price, autoKey);
                    }
                    builder.Append(cmd);
                    builder.AppendLine();
                    sqlList.Add(cmd);
                }

            }
            finally
            {
                if (rs != null)
                {
                    rs.Close();
                }
                conn.Close();
                conn = null;
            }

            txtSql.Text = builder.ToString();
        }

        private void backgroundWorker_DoWork(object sender, DoWorkEventArgs e)
        {
            this.progressBar.Show();
            btExecute.Enabled = false;
            txtPath.Text = openFileDialog.FileName;
            connectString = string.Format(connectStringPattern, openFileDialog.FileName);
            if (conn == null)
            {
                conn = new OleDbConnection(connectString);
                conn.Open();

            }
            else if (conn.State == System.Data.ConnectionState.Open || conn.State == System.Data.ConnectionState.Connecting)
            {
                conn.Close();
                conn.Dispose();
                conn = new OleDbConnection(connectString);
                conn.Open();
            }

            DataTable sheets = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            if (sheets != null && sheets.Rows.Count > 0)
            {
                foreach (DataRow r in sheets.Rows)
                {
                    string name = r["TABLE_NAME"].ToString();
                    if (name.Contains(@"$") && string.Compare(name, "stock$", true) == 0)
                    {
                        sheetName = name;
                    }
                }

                if (!string.IsNullOrEmpty(sheetName))
                {
                    getDataFormExcelFile();
                }
            }
        }

        private void backgroundWorker_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            lbProcessCount.Text = "Đang nhập dữ liệu đến dòng thứ :" + e.ProgressPercentage;
        }

        private void backgroundWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            lbProcessCount.Text = string.Empty;
            progressBar.Hide();
            btExecute.Enabled = true;
        }

        private void btExecute_Click(object sender, EventArgs e)
        {
            btExecute.Enabled = false;
            backgroundWorkerExecute.RunWorkerAsync();
        }

        private void backgroundWorkerExecute_DoWork(object sender, DoWorkEventArgs e)
        {
            progressBar.Show();
            Database.executeQuerys(sqlList, this.backgroundWorkerExecute);
        }

        private void backgroundWorkerExecute_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            progressBar.Show();
            lbProcessCount.Text = "Thực hiện tới dòng :" + e.ProgressPercentage;
        }

        private void backgroundWorkerExecute_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            progressBar.Hide();
            MessageBox.Show("Nạp dữ liệu thành công");
            lbProcessCount.Text = string.Empty;
            btExecute.Enabled = true;
        }
    }
}
